﻿using Quartz;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Media;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace SyncCuringData
{
    /*===================================================
	* 类名称: CuringJob
	* 类描述:
	* 创建人: 75259
	* 创建时间: 2018/12/25 11:22:17
	* 修改人: 
	* 修改时间:
	* 版本： @version 1.0
	=====================================================*/
    public class CuringJob : IJob
    {
        //第三方数据库链接字符串
        //string oracleConn = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.88)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=MES;Password=123456";
        //本地数据库链接字符串
        static string sqlserverConn = "Data Source=172.27.1.10;Initial Catalog=Lilaser.MES;Persist Security Info=True;User ID=sa;Password=ll@123456";

        //定时执行任务
        public Task Execute(IJobExecutionContext context)
        {
            Action action = () =>
            {
                //获取第三方提供的固化数据
                System.Data.DataTable curingChamberData = new System.Data.DataTable();

                LogHelper.Info($"襄阳固化数据同步开始,Time:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");
                //捕获错误
                string errorMessage = string.Empty;
                //获取第三方提供的固化数据 存放区
                List<CuringPlate> listCuringChamberData = new List<CuringPlate>();
                try
                {
                    try
                    {
                        listCuringChamberData = DataProcess.GetCuringPlateList("select t.* from hbltdata.zf_kw_ll_jbghsj t where issync = 'N'");
                    }
                    catch (Exception ex)
                    {
                        LogHelper.Error("获取第三方固化数据失败!", ex);
                    }

                    //将第三方固化数据插入一零服务器            
                    System.Data.DataTable curingDetailData = new System.Data.DataTable();//固化明细表
                    string[] curingDetailData_Columns = { "MEID", "CodeNO", "MatCode", "BatchBarCode", "Status", "CreateDate", "CreateUser", "UpdateDate", "UpdateUser", "EXT1", "EXT2", "EXT3", "EXT4", "EXT5", "DeleteFlag", "UpdateType", "SynStatus" };

                    //固化明细表添加列
                    System.Data.DataColumn[] dataColumnsHeader = new System.Data.DataColumn[curingDetailData_Columns.Length];
                    for (int i = 0; i < curingDetailData_Columns.Length; i++)
                    {
                        dataColumnsHeader[i] = new System.Data.DataColumn(curingDetailData_Columns[i], typeof(string));
                    }
                    curingDetailData.Columns.AddRange(dataColumnsHeader);

                    //LINQ分组
                    var thirdPartyCuringData = from e in listCuringChamberData.AsEnumerable()
                                               group e by new
                                               {

                                                   SCX = e.Scx,              //生产线
                                                   BANZU = e.Banzu,          //班组
                                                   BANCI = e.Banci,          //班次  
                                                   GZZX = e.Gzzx,            //工作中心
                                                   ROOMNO = e.Roomno,        //固化室号
                                                   ITIME_TYPE = e.Itime_type,//程序号
                                                   INPEOP = e.Inpeop,         //入固人
                                                   INDATE = e.Indate.Substring(0, 10)//操作日期
                                               } into entity
                                               select new
                                               {
                                                   SCX = entity.Key.SCX,
                                                   BANZU = entity.Key.BANZU,
                                                   BANCI = entity.Key.BANCI,
                                                   GZZX = entity.Key.GZZX,
                                                   ROOMNO = entity.Key.ROOMNO,
                                                   ITIME_TYPE = entity.Key.ITIME_TYPE,
                                                   INPEOP = entity.Key.INPEOP,
                                                   INDATE = entity.Key.INDATE.Substring(0, 10)
                                               };
                    //主表数据存放区
                    CuringPlate curingPlate = new CuringPlate();
                    if (thirdPartyCuringData.Count() > 0)
                    {
                        thirdPartyCuringData.ToList().ForEach(e =>
                        {
                            SqlConnection sqlConnection = new SqlConnection(sqlserverConn);
                            if (sqlConnection.State != ConnectionState.Open)
                            {
                                sqlConnection.Open();
                            }
                            SqlCommand sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlCommand.CommandText = "[dbo].[RM_CuringChamberMain_Insert]";
                            List<CuringPlate> list = listCuringChamberData.FindAll(CuringPlate =>
                            {
                                if (CuringPlate.Scx == e.SCX && CuringPlate.Banzu == e.BANZU && CuringPlate.Banci == e.BANCI && CuringPlate.Gzzx == e.GZZX && CuringPlate.Roomno == e.ROOMNO && CuringPlate.Itime_type == e.ITIME_TYPE && CuringPlate.Inpeop == e.INPEOP && CuringPlate.Indate.Substring(0, 10) == e.INDATE.Substring(0, 10)) { return true; } else { return false; }
                            });

                            curingPlate = list[0];
                            SqlParameter[] sqlParameters = new SqlParameter[]
                            {
                                new SqlParameter() { ParameterName = "@Basement",SqlDbType = SqlDbType.VarChar,Value = "X",Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@LineId",SqlDbType = SqlDbType.VarChar,Size=15,Value = curingPlate.Scx,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@Workshop",SqlDbType = SqlDbType.NVarChar,Size =50,Value = curingPlate.Gzzx,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@CuringNo",SqlDbType = SqlDbType.NVarChar,Size =25,Value = "GH"+curingPlate.Roomno,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@ShiftCode",SqlDbType = SqlDbType.NVarChar,Size =3,Value = curingPlate.Banci,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@GroupCode",SqlDbType = SqlDbType.NVarChar,Size =10,Value = curingPlate.Banzu,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@JobDate",SqlDbType = SqlDbType.NVarChar,Size =20,Value = curingPlate.Indate,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@JobPeople",SqlDbType = SqlDbType.NVarChar,Size =5,Value = curingPlate.Inpeop,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@State",SqlDbType = SqlDbType.NVarChar,Size =2,Value = "1",Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@EXT1",SqlDbType = SqlDbType.NVarChar,Size =50,Value = string.IsNullOrEmpty(curingPlate.Itime_type)==true?"空数据":curingPlate.Itime_type,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@EXT2",SqlDbType = SqlDbType.NVarChar,Size =50,Value = string.IsNullOrEmpty(curingPlate.Usedate)==true?"空数据":curingPlate.Usedate,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@EXT3",SqlDbType = SqlDbType.NVarChar,Size =50,Value = string.IsNullOrEmpty(curingPlate.Cplace_sj)==true?"空数据":curingPlate.Cplace_sj,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@EXT4",SqlDbType = SqlDbType.NVarChar,Size =50,Value = string.IsNullOrEmpty(curingPlate.Rkdate)==true?"空数据":curingPlate.Rkdate,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@DeleteFlag",SqlDbType = SqlDbType.NVarChar,Size =1,Value = "A",Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@UpdateType",SqlDbType = SqlDbType.Int,Size =1,Value = 0,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@SynStatus",SqlDbType = SqlDbType.Int,Size =1,Value = 0,Direction = ParameterDirection.Input},
                                new SqlParameter() { ParameterName = "@CodeNO",SqlDbType = SqlDbType.NVarChar,Size =50,Direction = ParameterDirection.Output}
                            };
                            sqlCommand.Parameters.AddRange(sqlParameters);
                            sqlCommand.ExecuteNonQuery();
                            string back_CodeNo = sqlParameters[sqlParameters.Length - 1].SqlValue.ToString();//固化主表添加成功后返回投料批次号 添加失败返回"";
                            if (!string.IsNullOrEmpty(back_CodeNo))
                            {
                                foreach (var entity in list)
                                {
                                    DataRow newRow = curingDetailData.NewRow();
                                    newRow[0] = Guid.NewGuid().ToString();
                                    newRow[1] = back_CodeNo;
                                    newRow[2] = "JB";
                                    newRow[3] = entity.Cbarcode;
                                    newRow[4] = "1";
                                    newRow[5] = string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);
                                    newRow[6] = entity.Inpeop;
                                    newRow[9] = entity.Invmodal;
                                    newRow[14] = "A";
                                    curingDetailData.Rows.Add(newRow);
                                }

                                SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
                                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints, sqlTransaction);
                                sqlBulkCopy.DestinationTableName = "RM_CuringBusinessDetail";
                                for (int i = 0; i < curingDetailData.Columns.Count; i++)
                                {
                                    sqlBulkCopy.ColumnMappings.Add(i, i);
                                }

                                try
                                {
                                    sqlBulkCopy.WriteToServer(curingDetailData);
                                    sqlTransaction.Commit();
                                    //更新同步过的数据
                                    string strDelSQL = $"update hbltdata.zf_kw_ll_jbghsj set issync = 'Y',sysnc_time = sysdate where SCX = '{curingPlate.Scx}' and BANZU = '{curingPlate.Banzu}' and BANCI = '{curingPlate.Banci}' and GZZX = '{curingPlate.Gzzx}' and ROOMNO = '{curingPlate.Roomno}' and ITIME_TYPE = '{curingPlate.Itime_type}' and INPEOP = '{curingPlate.Inpeop}' and substr(INDATE,1,10)= '{curingPlate.Indate.Substring(0, 10)}'";
                                    DataProcess.UpdateCuringPlateData(strDelSQL);
                                }
                                catch (Exception ex)
                                {
                                    errorMessage += "①:" + ex.Message;
                                    LogHelper.Error("数据插入襄阳服务器(10)失败!", ex);
                                    sqlTransaction.Rollback();

                                }
                                finally
                                {
                                    sqlBulkCopy.Close();
                                    sqlConnection.Close();
                                    //清除明细表中的数据
                                    curingDetailData.Clear();
                                }
                            }
                        });

                    }
                }
                catch (Exception ex)
                {
                    errorMessage += "②:" + ex.Message;
                    LogHelper.Error("CuringLogJob 运行异常", ex);
                }
                LogHelper.Info($"襄阳固化数据同步结束,Time:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");
            };

            return Task.Factory.StartNew(action);
        }
    }
}
